package com.itheima.dao;

import com.itheima.pojo.BlogSort;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;
import java.util.Map;


public interface BlogSortMapper extends Mapper<BlogSort> {

    @Select("SELECT b.blog_sort_uid blogSortUid,bs.sort_name name,count(b.uid) value FROM t_blog b LEFT JOIN t_blog_sort bs  ON bs.uid = b.blog_sort_uid where b.blog_sort_uid is not null and bs.sort_name is not null GROUP BY b.blog_sort_uid")
    @Results({
            @Result(column = "blogSortUid",property = "blogSortUid"),
            @Result(column = "name",property = "name"),
            @Result(column = "value",property = "value")
    })
    List<Map> getBlogCountByBlogSort();

    @Select("SELECT max(sort)FROM `t_blog_sort`")
    Long selectMaxSort();

    @Select("SELECT DATE_FORMAT(create_time,'%Y%m') months FROM t_blog GROUP BY months")
    List<String> getSortList();

    //查询blogSort的引用量
    @Select("SELECT \n" +
            "\ttbs.*, count(1) \n" +
            "FROM\n" +
            "\tt_blog_sort tbs LEFT JOIN t_blog tb\n" +
            "\tON tbs.uid = tb.blog_sort_uid\n" +
            "WHERE \n" +
            "\tis_original = '0'\n" +
            "GROUP BY\n" +
            "\ttbs.uid\n" +
            "ORDER BY\n" +
            "\tcount(1) DESC")
    List<Map<String,Object>> blogSortByCite();

    @Select("SELECT content FROM t_blog WHERE create_time like  #{month} or update_time like #{month}")
    List<String> getArticleByMonth(String month);
}
